﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class AppointmentsInfoDAL : DBBase
    {
        public static AppointmentsItem LoadAppointment(string id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [APPOINTMENTS_ID]
                                                          ,[LOAN_ID]
                                                          ,[APPOINTMENTS_DATE]
                                                          ,[APPOINTMENTS_START_TIME]
                                                          ,[APPOINTMENTS_END_TIME]
                                                          ,[APPOINTMENTS_STATUS]
                                                      FROM [CAR_LOAN].[dbo].[APPOINTMENTS] where APPOINTMENTS_ID = @LOAN_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    AppointmentsItem item = new AppointmentsItem();
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    item.APPOINTMENTS_ID = Convert.ToInt32(dt.Rows[0]["APPOINTMENTS_ID"].ToString());
                    item.APPOINTMENTS_DATE = Convert.ToDateTime(dt.Rows[0]["APPOINTMENTS_DATE"].ToString());
                    item.APPOINTMENTS_START_TIME = dt.Rows[0]["APPOINTMENTS_START_TIME"].ToString();
                    item.APPOINTMENTS_END_TIME = dt.Rows[0]["APPOINTMENTS_END_TIME"].ToString();
                    item.APPOINTMENTS_STATUS = dt.Rows[0]["APPOINTMENTS_STATUS"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAppointmentALL(int empId,string loanId = "",string name = "",string lastName = "")
        {
            try
            {
                DBBase.ConncetDatabase();
                string sqlQuery = @"SELECT dbo.APPOINTMENTS.APPOINTMENTS_ID, dbo.APPOINTMENTS.APPOINTMENTS_DATE,
                                                dbo.APPOINTMENTS.APPOINTMENTS_START_TIME + ' - ' + 
                                                dbo.APPOINTMENTS.APPOINTMENTS_END_TIME as APPOINTMENTS_TIME,
                                                dbo.APPOINTMENTS.LOAN_ID, dbo.CUSTOMER.CUSTOMER_NAME, dbo.CUSTOMER.CUSTOMER_LASTNAME, 
                                                dbo.CUSTOMER.CUSTOMER_TEL, dbo.CUSTOMER.CUSTOMER_ID, dbo.SCHEDULE.EMPLOYEE_ID
                                                    FROM dbo.APPOINTMENTS INNER JOIN
                                                dbo.LOAN ON dbo.APPOINTMENTS.LOAN_ID = dbo.LOAN.LOAN_ID INNER JOIN
                                                dbo.CUSTOMER ON dbo.LOAN.CUSTOMER_ID = dbo.CUSTOMER.CUSTOMER_ID INNER JOIN
                                                dbo.SCHEDULE ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID
                                                WHERE dbo.LOAN.LOAN_STATUS = 'A' and dbo.SCHEDULE.EMPLOYEE_ID = @EMPLOYEE_ID";

                if (loanId != "") 
                {
                    sqlQuery += " And dbo.LOAN.LOAN_ID = " + loanId;
                }
                if (name != "")
                {
                    sqlQuery += " And dbo.CUSTOMER.CUSTOMER_NAME like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " And dbo.CUSTOMER.CUSTOMER_LASTNAME like '%" + lastName + "%'";
                }

                SqlDataReader dr;
                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", empId);

                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAppointmentForCheck(string date)
        {
            try
            {
                DBBase.ConncetDatabase();
                string sqlQuery = @"SELECT [APPOINTMENTS_ID]
                                  ,[LOAN_ID]
                                  ,[APPOINTMENTS_DATE]
                                  ,[APPOINTMENTS_START_TIME]
                                  ,[APPOINTMENTS_END_TIME]
                                  ,[APPOINTMENTS_STATUS]
                              FROM [CAR_LOAN].[dbo].[APPOINTMENTS] 
                              where [APPOINTMENTS_STATUS] = 'A'
                               AND [APPOINTMENTS_DATE] < '" + date + "'";
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static int LoadQue(string date,int empId)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT dbo.APPOINTMENTS.APPOINTMENTS_ID, dbo.APPOINTMENTS.LOAN_ID, dbo.APPOINTMENTS.APPOINTMENTS_DATE, 
                                                dbo.APPOINTMENTS.APPOINTMENTS_START_TIME, dbo.APPOINTMENTS.APPOINTMENTS_END_TIME
                                                    FROM dbo.APPOINTMENTS INNER JOIN
                                                dbo.LOAN ON dbo.APPOINTMENTS.LOAN_ID = dbo.LOAN.LOAN_ID INNER JOIN
                                                dbo.SCHEDULE ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID 
                                                where dbo.APPOINTMENTS.APPOINTMENTS_DATE = @APPOINTMENTS_DATE
                                                and  dbo.SCHEDULE.EMPLOYEE_ID = @EMPLOYEE_ID"
                                                       , DBBase.con);
                command.Parameters.AddWithValue("@APPOINTMENTS_DATE", date);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", empId);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt.Rows.Count;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        public static void Insert(AppointmentsItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[APPOINTMENTS]
                                                       ([LOAN_ID]
                                                       ,[APPOINTMENTS_DATE]
                                                       ,[APPOINTMENTS_START_TIME]
                                                       ,[APPOINTMENTS_END_TIME]
                                                       ,[APPOINTMENTS_STATUS])
                                                 VALUES
                                                      ( @LOAN_ID
                                                       ,@APPOINTMENTS_DATE
                                                       ,@APPOINTMENTS_START_TIME
                                                       ,@APPOINTMENTS_END_TIME
                                                       ,@APPOINTMENTS_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@APPOINTMENTS_DATE", item.APPOINTMENTS_DATE);
                command.Parameters.AddWithValue("@APPOINTMENTS_START_TIME", item.APPOINTMENTS_START_TIME);
                command.Parameters.AddWithValue("@APPOINTMENTS_END_TIME", item.APPOINTMENTS_END_TIME);
                command.Parameters.AddWithValue("@APPOINTMENTS_STATUS", item.APPOINTMENTS_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool Update(AppointmentsItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[APPOINTMENTS]
                                               SET [APPOINTMENTS_STATUS] = @APPOINTMENTS_STATUS
                                             WHERE [LOAN_ID] = @LOAN_ID", DBBase.con);

                command.Parameters.AddWithValue("@APPOINTMENTS_STATUS", item.APPOINTMENTS_STATUS);
                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
                return true;
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
